package day13;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;




public class StudentManager {
	//作为全局变量，让所有方法都可以使用
		private static ArrayList<Student> list = new ArrayList<Student>();
		private static Scanner scanner = new Scanner(System.in);

		public static void main(String[] args) {
			// 从数据库中查询所有的学生信息
			list = selectAll();
			
			while (true) {
				System.out.println("1、打印所有学生");
				System.out.println("2、添加学生");
				System.out.println("3、删除学生");
				System.out.println("4、查找学生");
				System.out.println("5、修改学生");
				System.out.println("0、退出系统");
				int type = scanner.nextInt();
				if (type == 0) {
					System.out.println("退出管理系统");
					break;
				}
				
				switch (type) {
				case 1:// 打印所有学生
					printAllStudent();
					break;
				case 2:
					insertStudent();
					break;
				case 3:
					deleteStudent();
					break;
				case 4:
					updateStudent();
					break;
				default:
					break;
				}
			}
		}
		
		private static void updateStudent() {
			System.out.println("请输入要更新的学生id");
			int id = scanner.nextInt();
			System.out.println("请输入要更新的名字：");
			String name = scanner.next();
			System.out.println("请输入要更新的年龄：");
			int age = scanner.nextInt();
			System.out.println("请输入要更新的性别：");
			String gender = scanner.next();
			
			// 根据id更新只会更新唯一的一条
			String sql = "update student set name=?,age=?,gender=? where id=?";
			// 根据名字可能会更新多条
			// String sql1 = "update student set name=?,age=?,gender=? where name=?";
			
			list = selectAll();
		}

		private static void deleteStudent() {
			System.out.println("请输入要删除的学生id");
			int id = scanner.nextInt();
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			
			try { // DBUtils
				connection = JDBCUtil.getConnection();
				String sql = "";
				preparedStatement = connection.prepareStatement(sql);
				resultSet = preparedStatement.executeQuery();
				// 判断下一行有没有，有返回true，并指向这一行，没有返回false
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String name = resultSet.getString("name");
					int age = resultSet.getInt("age");
					String gender = resultSet.getString("gender");
					Student student = new Student(id, name, age, gender);
					list.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(connection, preparedStatement, resultSet);
			}
			
			return list;
			}
		

		// 添加学生时候不需要输入id，因为id是数据库管理，自增
		private static void insertStudent() {
			System.out.println("请输入学生的名字");
			String name = scanner.next();
			System.out.println("请输入年龄");
			int age = scanner.nextInt();
			System.out.println("请输入性别");
			String gender = scanner.next();
			
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			try {
				connection = JDBCUtil.getConnection();
				String sql = "INSERT INTO student(`name`,age,gender) VALUES(?,?,?)";
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setString(1, name);
				preparedStatement.setInt(2, age);
				preparedStatement.setString(3, gender);
				int count = preparedStatement.executeUpdate();
				if (count == 1) {
					System.out.println("插入成功");
				} else {
					System.out.println("插入失败");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(connection, preparedStatement, null);
			}
			
			// 数据已经插入到数据库里面，但是打印的ArrayList<Student> list数据是没有添加
			// 刚才插入的Student的数据，这里处理方式有两种：
			// 1、jdbc在自增条件下插入数据，是可以返回那个自增的id的
			// 2、重新去查询一下，虽然这样有些麻烦，但是已经封住成一个方法，可以直接调用
			// list.add(student);
			// 在后面的web开发中，做了添加、修改、删除这些操作后需要重新查询一下数据
			// 需要和数据库数据保持一致
			list = selectAll();
		}

		private static void printAllStudent() {
			for (Student student : list) {
				System.out.println(student);
			}
		}
		
		public static ArrayList<Student> selectAll() {
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			ArrayList<Student> list = new ArrayList<Student>();
			// 加载驱动
			try { // DBUtils
				connection = JDBCUtil.getConnection();
				String sql = "select id,name,age,gender from student";
				preparedStatement = connection.prepareStatement(sql);
				resultSet = preparedStatement.executeQuery();
				// 判断下一行有没有，有返回true，并指向这一行，没有返回false
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String name = resultSet.getString("name");
					int age = resultSet.getInt("age");
					String gender = resultSet.getString("gender");
					Student student = new Student(id, name, age, gender);
					list.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(connection, preparedStatement, resultSet);
			}
			
			return list;
		}

}
